﻿using DatabaseCore.Dto;
using NawaData.Dao;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DatabaseCore
{
    public class OrderDetailDao : IOrderDetailDao
    {
        public bool Insert(List<tbl_OrderDetail> entity)
        {
            using (var ctx = new ExportDBEntities())
            {
                foreach (var item in entity)
                {
                    ctx.tbl_OrderDetail.Add(item);
                }

                return ctx.SaveChanges() > 0;
            }
        }

        public bool Insert(tbl_OrderDetail entity)
        {
            using (var ctx = new ExportDBEntities())
            {
                ctx.tbl_OrderDetail.Add(entity);

                return ctx.SaveChanges() > 0;
            }
        }

        public bool Update(tbl_OrderDetail entity)
        {
            using (var ctx = new ExportDBEntities())
            {
                var orderDetail = (from x in ctx.tbl_OrderDetail
                                   where x.OrderNo == entity.OrderNo
                                   && x.ItemNo == entity.ItemNo
                                   select x).FirstOrDefault();

                orderDetail.ItemId = entity.ItemId;
                orderDetail.MaterialNo = entity.MaterialNo;
                orderDetail.Plant = entity.Plant;
                orderDetail.Qty = entity.Qty;
                orderDetail.Amount = entity.Amount;
                orderDetail.Package = entity.Package;
                orderDetail.Weight = entity.Weight;
                orderDetail.Comment = entity.Comment;
                orderDetail.ApproveQty = entity.ApproveQty;

                //คำนวณจำนวนสต็อกที่ตัดได้ และที่ต้องไปเข้าคิว
                //orderDetail.ReceivedQty = orderDetail.TransferQty;
                //orderDetail.RemainingQty = orderDetail.ReceivedQty - orderDetail.TransferQty;

                orderDetail.UpdateBy = entity.UpdateBy;
                orderDetail.UpdateDate = entity.UpdateDate;
                orderDetail.Rate = entity.Rate;
                orderDetail.ValidFrom = entity.ValidFrom;
                orderDetail.ValidTo = entity.ValidTo;

                return ctx.SaveChanges() > 0;
            }
        }

        public bool Update(List<tbl_OrderDetail> entity, string orderNo, string userName)
        {
            using (var ctx = new ExportDBEntities())
            {
                int i = 0;
                foreach (var item in entity)
                {
                    var orderDetail = (from x in ctx.tbl_OrderDetail
                                       where x.OrderNo == item.OrderNo
                                       && x.ItemNo == item.ItemNo
                                       select x).FirstOrDefault();

                    // add new row
                    if (orderDetail == null)
                    {
                        tbl_OrderDetail newOrderDetail = entity[i];
                        newOrderDetail.OrderNo = orderNo;
                        newOrderDetail.ItemNo = i +1;
                        newOrderDetail.CreateBy = userName;
                        newOrderDetail.CreateDate = DateTime.Today;
                        newOrderDetail.UpdateBy = userName;
                        newOrderDetail.UpdateDate = DateTime.Today;
                        newOrderDetail.ReceivedQty = 0;
                        newOrderDetail.RemainingQty = newOrderDetail.Qty;
                        ctx.tbl_OrderDetail.Add(newOrderDetail);
                    }
                    else
                    {
                        orderDetail.ItemId = item.ItemId;
                        //orderDetail.MaterialNo = item.MaterialNo;
                        orderDetail.Plant = item.Plant;
                        orderDetail.Qty = item.Qty;
                        orderDetail.Amount = item.Amount;
                        orderDetail.Package = item.Package;
                        orderDetail.Weight = item.Weight;
                        orderDetail.Comment = item.Comment;
                        orderDetail.ApproveQty = item.ApproveQty;

                        //คำนวณจำนวนสต็อกที่ตัดได้ และที่ต้องไปเข้าคิว
                        //orderDetail.ReceivedQty = orderDetail.TransferQty;
                        //orderDetail.RemainingQty = orderDetail.ReceivedQty - orderDetail.TransferQty;

                        orderDetail.UpdateBy = item.UpdateBy;
                        orderDetail.UpdateDate = item.UpdateDate;
                        orderDetail.Rate = item.Rate;
                        orderDetail.ValidFrom = item.ValidFrom;
                        orderDetail.ValidTo = item.ValidTo;
                    }
                    i++;
                }

                return ctx.SaveChanges() > 0;
            }
        }

        public bool Delete(string OrderNo, string MatNo)
        {
            using (var ctx = new ExportDBEntities())
            {
                var deleteOrderDetail = ctx.tbl_OrderDetail.FirstOrDefault(i => i.OrderNo == OrderNo && i.MaterialNo == MatNo);
                if (deleteOrderDetail != null)
                {
                    ctx.tbl_OrderDetail.Remove(deleteOrderDetail);
                }
                return ctx.SaveChanges() > 0;
            }
        }
        
        public bool Delete(List<tbl_OrderDetail> entity)
        {
            using (var ctx = new ExportDBEntities())
            {
                foreach (var item in entity)
                {
                    var deleteOrderDetail = ctx.tbl_OrderDetail.FirstOrDefault(i => i.OrderNo == item.OrderNo && i.MaterialNo == item.MaterialNo);
                    if (deleteOrderDetail != null)
                    {
                        ctx.tbl_OrderDetail.Remove(deleteOrderDetail);
                    }
                }

                return ctx.SaveChanges() > 0;
            }
        }

        public bool DeleteByOrderNo(string OrderNo)
        {
            using (var ctx = new ExportDBEntities())
            {
                int result = ctx.Database.ExecuteSqlCommand("DELETE FROM tbl_OrderDetail WHERE OrderNo = @OrderNo ", new[] { new SqlParameter("@OrderNo", OrderNo) });
                return result > 0 ;
            }
        }

        public IList<tbl_OrderDetail> SearchByOrderNo(string orderNo)
        {
            using (var ctx = new ExportDBEntities())
            {
                var orderDetails = from x in ctx.tbl_OrderDetail
                                   where x.OrderNo == orderNo
                                   select x;

                var productIds = orderDetails.Select(o => o.MaterialNo).ToArray();
                var productDao = new ProductsDao();
                var productNames = productDao.getProductNames(productIds);
                foreach (var d in orderDetails)
                {
                    foreach (var name in productNames)
                    {
                        if (d.MaterialNo == name.mmatno)
                        {
                            d.ItemName = name.mmateng;
                        }
                    }
                }
                
                if (orderDetails == null)
                    return null;

                return orderDetails.ToList();
            }
        }

        public IList<ExportObject> SearchByOrderNoExport(string orderNo)
        {
            using (var ctx = new ExportDBEntities())
            {
                var orderDetails = (from x in ctx.tbl_OrderDetail
                                    join d in ctx.tbl_Order on x.OrderNo equals d.OrderNo
                                    join ep in ctx.tbl_PlantDomExport on x.Plant equals ep.PlantDom
                                    where x.OrderNo == orderNo
                                    select new ExportObject(){
                                    OrderNo=  x.OrderNo,
                                    MaterialNo = x.MaterialNo,
                                    Qty= x.Qty,
                                    ReceivedQty =  x.ReceivedQty,
                                    RemainingQty= x.RemainingQty,
                                    Plant = x.Plant,
                                    PlantExport = ep.PlantExport,
                                    RefOrderNo = d.RefOrderNo,
                                    ReviseQty = x.ReviseQty ?? 0,
                                    isDelete = x.IsDeleteFromQueue == true ? "Delete" : ""
                                   }).ToList();

                var productIds = orderDetails.Select(o => o.MaterialNo).ToArray();
                var productDao = new ProductsDao();
                var productNames = productDao.getProductNames(productIds);
                foreach (var d in orderDetails)
                {
                    foreach (var name in productNames)
                    {
                        if (d.MaterialNo == name.mmatno)
                        {
                            d.MaterialNameEng = name.mmateng;
                        }
                    }
                }

                if (orderDetails == null)
                    return null;

                return orderDetails.ToList();
            }
        }

        public tbl_OrderDetail GetOrderDetail(string orderNo, int itemNo)
        {
            using (var ctx = new ExportDBEntities())
            {
                var orderDetail = from x in ctx.tbl_OrderDetail
                                  where x.OrderNo == orderNo
                                  && x.ItemNo == itemNo
                                  select x;

                if (orderDetail == null)
                {
                    return null;
                }

                return orderDetail.FirstOrDefault();
            }
        }

        public bool UpdateReceivedQty(string orderNo, int itemNo, int receivedQty, string updateBy, int? approvedQty, string plant)
        {
            //var orderDetail = GetOrderDetail(orderNo, itemNo);

            using (var ctx = new ExportDBEntities())
            {
                var orderDetail = (from x in ctx.tbl_OrderDetail
                                   where x.OrderNo == orderNo
                                   && x.ItemNo == itemNo && x.RemainingQty > 0
                                   select x).FirstOrDefault();

                if (orderDetail == null)
                    return false;


                orderDetail.ReceivedQty += receivedQty;
                orderDetail.RemainingQty -= receivedQty;
                orderDetail.ApproveQty = approvedQty;

                orderDetail.UpdateBy = updateBy;
                orderDetail.UpdateDate = DateTime.Now;
                orderDetail.Plant = plant;
                return ctx.SaveChanges() > 0;
            }
        }

        public bool UpdateReceivedQtyWithMatNo(string orderNo, string Matno, int receivedQty, string updateBy)
        {
            //var orderDetail = GetOrderDetail(orderNo, itemNo);

            using (var ctx = new ExportDBEntities())
            {
                var orderDetail = (from x in ctx.tbl_OrderDetail
                                   where x.OrderNo == orderNo
                                   && x.MaterialNo == Matno && x.RemainingQty > 0
                                   select x).FirstOrDefault();

                if (orderDetail == null)
                    return false;

                var ReviseQty = orderDetail.ReviseQty != null ? orderDetail.ReviseQty += receivedQty : receivedQty;
                orderDetail.ReviseQty = ReviseQty;
                orderDetail.RemainingQty -= receivedQty;
                orderDetail.IsReviseQtyFromQueue = true;
                orderDetail.UpdateBy = updateBy;
                orderDetail.UpdateDate = DateTime.Now;
                return ctx.SaveChanges() > 0;
            }
        }

        public bool UpdateReceivedPlantWithMatNo(string orderNo, string Matno, string plant, string updateBy)
        {
            //var orderDetail = GetOrderDetail(orderNo, itemNo);

            using (var ctx = new ExportDBEntities())
            {
                var orderDetail = (from x in ctx.tbl_OrderDetail
                                   where x.OrderNo == orderNo
                                   && x.MaterialNo == Matno && x.RemainingQty > 0
                                   select x).FirstOrDefault();

                if (orderDetail == null)
                    return false;

                orderDetail.Plant = plant;
                orderDetail.IsRevisePlantFromQueue = true;
                orderDetail.UpdateBy = updateBy;
                orderDetail.UpdateDate = DateTime.Now;
                return ctx.SaveChanges() > 0;
            }
        }


        public bool UpdateQueueMapQty(List<tbl_Transaction> ts)
        {

            //var orderDetail = GetOrderDetail(orderNo, itemNo);

            using (var ctx = new ExportDBEntities())
            {
                foreach (var t in ts)
                {
                    var orderDetail = (from x in ctx.tbl_OrderDetail
                                       where x.OrderNo == t.OrderNo
                                       && x.MaterialNo == t.MaterialNo && x.RemainingQty > 0
                                       select x).FirstOrDefault();

                    if (orderDetail != null)
                    {
                        //need to reverse to +1 because ts.q come with -1
                        orderDetail.ReceivedQty += (-1 * t.Qty);
                        orderDetail.RemainingQty -= (-1 * t.Qty);
                        orderDetail.UpdateBy = "Queue Map";
                        orderDetail.UpdateDate = DateTime.Now;
                    }
                }
                
                return ctx.SaveChanges() > 0;
            }
        }

        public bool UpdateRemainingQtyByDeleteQueue(string orderNo, string materialNo, int mappedQty, string updateBy)
        {
            using (var ctx = new ExportDBEntities())
            {
                var orderDetail = (from x in ctx.tbl_OrderDetail
                                   where x.OrderNo == orderNo
                                   && x.MaterialNo == materialNo
                                   select x).FirstOrDefault();

                if (orderDetail == null)
                    return false;


                orderDetail.RemainingQty -= mappedQty;

                orderDetail.UpdateBy = updateBy;
                orderDetail.UpdateDate = DateTime.Now;

                return ctx.SaveChanges() > 0;
            }
        }

        public bool IsOrderDetailCompleted(string orderNo)
        {
            using (var ctx = new ExportDBEntities())
            {
                var orderDetails = from x in ctx.tbl_OrderDetail
                                   where x.OrderNo == orderNo
                                    && x.RemainingQty != 0
                                   select x;

                //All of OrderDetail is not waiting in queue, completed
                if (orderDetails == null || orderDetails.Count() == 0)
                    return true;

                return false;
            }
        }

        public bool UpdateRemainingQtyByChangePlant(string orderNo, string materialNo, string updateBy)
        {
            using (var ctx = new ExportDBEntities())
            {
                var orderDetail = (from x in ctx.tbl_OrderDetail
                                   where x.OrderNo == orderNo
                                   && x.MaterialNo == materialNo
                                   select x).FirstOrDefault();

                if (orderDetail == null)
                    return false;


                orderDetail.RemainingQty = orderDetail.Qty;
                orderDetail.ReceivedQty = 0;

                orderDetail.UpdateBy = updateBy;
                orderDetail.UpdateDate = DateTime.Now;

                return ctx.SaveChanges() > 0;
            }
        }

        public List<BackOrderExport> GetBackOrder()
        {
            using (var ctx = new ExportDBEntities())
            {
                var backorder = from x in ctx.tbl_OrderDetail
                                join o in ctx.tbl_Order on x.OrderNo equals o.OrderNo
                                where x.RemainingQty > 0 && x.Plant != null && x.IsDeleteFromQueue != true && o.Status == 3
                                select x;

                var result = backorder.GroupBy(g => new { g.MaterialNo, g.Plant }).Select(group => new BackOrderExport { Plant = group.FirstOrDefault().Plant,
                MaterialNo = group.FirstOrDefault().MaterialNo,
                RemainingQty = group.Sum(g2=>g2.RemainingQty)});
                return result.ToList();
            }
        }

        public void SaveComment(List<Comment> comments)
        {
            using (var ctx = new ExportDBEntities())
            {
                foreach (Comment c in comments)
                {
                    var comment = (from dd in ctx.tbl_OrderDetail
                                    where dd.OrderNo == c.OrderNo && dd.MaterialNo == c.MatNo
                                    select dd).FirstOrDefault();

                    if (comment != null)
                    {
                        comment.Comment = c.CommentText;
                    }
                }
                ctx.SaveChanges();
            }
        }
    }
}
